﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace PersonalAccounting.DataAccess
{
    public class DAOSaldo : IDAO<SaldoVO>
    {

        #region IDAO<SaldoVO> Members

        public bool Select(SaldoVO obj)
        {
            throw new NotImplementedException();
        }

        public void Load(SaldoVO obj, System.Data.SQLite.SQLiteDataReader reader)
        {
            obj.Cuenta.Codigo = Convert.ToInt32(reader["IdCta"]);
            obj.Saldo = Convert.ToDouble(reader["Saldo"]);

            IDAO<CuentaVO> dao = new DAOCuenta();
            dao.Select(obj.Cuenta);
        }

        public bool Insert(SaldoVO obj)
        {
            throw new NotImplementedException();
        }

        public bool Update(SaldoVO obj)
        {
            throw new NotImplementedException();
        }

        public bool Delete(SaldoVO obj)
        {
            throw new NotImplementedException();
        }

        public List<SaldoVO> GetAll()
        {
            throw new NotImplementedException();
        }

        #endregion

        #region Public Methods

        public List<SaldoVO> GetSaldos(DateTime fecha)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteParameter parFecha = null;
            SQLiteDataReader reader = null;
            List<SaldoVO> lista = new List<SaldoVO>();
            SaldoVO saldo = null;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QueryGetSaldos();

            parFecha = new SQLiteParameter("@fecha", fecha);
            cmd.Parameters.Add(parFecha);

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                saldo = new SaldoVO();
                Load(saldo, reader);
                lista.Add(saldo);
            }

            return lista;
        }

        #endregion

        
        #region SQL
        
        private string QueryGetSaldos()
        {
            return "SELECT IdCta, Sum(Importe) AS Saldo " +
                   "FROM Movtos " +
                   "WHERE Fecha <= @fecha " +
                   "GROUP BY IdCta";
        }

        #endregion

    }
}